package com.example.othree.mapper;

import com.example.othree.entity.TowerOzoneGradientObservations;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.type.LocalDateTimeTypeHandler;
import org.apache.ibatis.type.LocalDateTypeHandler;
import org.springframework.stereotype.Component;

import java.time.LocalDateTime;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author guoxixi
 * @since 2024-09-27
 */
@Component
public interface TowerOzoneGradientObservationsMapper extends BaseMapper<TowerOzoneGradientObservations> {

    /**
     * 逐分钟查询(单要素)
     */
    @Select("SELECT DATE_FORMAT(time, '%Y-%m-%d %H:%i') AS time, ${element} AS avgValue, pointName " +
            "FROM tower_ozone_gradient_observations " +
            "WHERE time BETWEEN #{startTime} AND #{endTime} " +
            "and pointName LIKE CONCAT('%', #{stationName}, '%') " +
            "ORDER BY pointName, time")
    List<Map<String, Object>> getMinuteData(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("stationName") String stationName,
            @Param("element") String element);

//    @Select("SELECT time, ${element} AS value, pointName " +
//            "FROM tower_ozone_gradient_observations " +
//            "WHERE time BETWEEN #{startTime} AND #{endTime} " +
//            "and pointName LIKE CONCAT('%', #{stationName}, '%') " +
//            "ORDER BY pointName, time")
//    /**
//     * mysql5.1.32不支持LocalDateTime,用Date，已解决--mysql驱动版本改为5.1.37
//     */
//    List<Map<String, Object>> getMinuteData(
//            @Param("startTime") Date startTime,
//            @Param("endTime") Date endTime,
//            @Param("stationName") String stationName,
//            @Param("element") String element);

    /**
     * 按小时分组查询(单要素)
     */
    @Select("SELECT DATE_FORMAT(time, '%Y-%m-%d %H:00:00') AS time, " +
            "AVG(${element}) AS avgValue, pointName " +
            "FROM tower_ozone_gradient_observations " +
            "WHERE time BETWEEN #{startTime} AND #{endTime} " +
            "AND pointName LIKE CONCAT('%', #{stationName}, '%') " +
            "GROUP BY pointName, DATE_FORMAT(time, '%Y-%m-%d %H:00:00')" +
            " Order by DATE_FORMAT(time, '%Y-%m-%d %H:00:00')")
    List<Map<String, Object>> getHourlyAverageData(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("stationName") String stationName,
            @Param("element") String element);

    @Select("SELECT " +
            "AVG(${element}) AS avgValue, pointName " +
            "FROM tower_ozone_gradient_observations " +
            "WHERE time BETWEEN #{startTime} AND #{endTime} " +
            "AND pointName LIKE CONCAT('%', #{stationName}, '%') " +
            "GROUP BY pointName " +
            "Order by time")
    List<Map<String, Object>> getHourly8AverageData(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("stationName") String stationName,
            @Param("element") String element);

    /**
     * 按天分组查询(单要素)
     */
    @Select("SELECT pointName, AVG(${element}) AS avgValue, DATE_FORMAT(time, '%Y-%m-%d') AS time " +
            "FROM tower_ozone_gradient_observations " +
            "WHERE time BETWEEN #{startTime} AND #{endTime} " +
            "AND pointName LIKE CONCAT('%', #{stationName}, '%') " +
            "GROUP BY pointName, DATE_FORMAT(time, '%Y-%m-%d')")
    List<Map<String, Object>> getDailyAverageData(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("stationName") String stationName,
            @Param("element") String element);

    /**
     * 8 小时滑动平均查询
     */
    @Select("SELECT time, pointName, AVG(${element}) " +
            "FROM tower_ozone_gradient_observations " +
            "WHERE time BETWEEN #{startTime} AND #{endTime} " +
            "AND pointName LIKE CONCAT('%', #{stationName}, '%')" +
    "ORDER BY PointName, time")
    List<Map<String, Object>> get8HourAverageData(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("stationName") String stationName,
            @Param("element") String element);
    /**
     * 8 小时滑动平均查询(全部要素)
     */
    @Select("SELECT " +
            "AVG(o3) as avg_o3, AVG(co) as avg_co, AVG(so2) as avg_so2, AVG(no2) as avg_no2, pointName " +
            "FROM tower_ozone_gradient_observations " +
            "WHERE time BETWEEN #{startTime} AND #{endTime} " +
            "AND pointName LIKE CONCAT('%', #{stationName}, '%') " +
            "GROUP BY pointName " +
            "Order by time")
    List<TowerOzoneGradientObservations> getHourly8AverageAllData(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("stationName") String stationName);

    /**
     * 逐分钟查询(所有要素)
     */
    @Select("SELECT time, pointName, o3, no2, so2, co " +
            "FROM tower_ozone_gradient_observations " +
            "WHERE time BETWEEN #{startTime} AND #{endTime} " +
            "AND pointName LIKE CONCAT('%', #{stationName}, '%') " +
            "ORDER BY pointName, time")
    List<TowerOzoneGradientObservations> getAllData(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("stationName") String stationName);

    /**
     * 按小时分组查询(所有要素)
     */
    @Select("SELECT DATE_FORMAT(time, '%Y-%m-%d %H:00:00') AS hour_time, " +
            "AVG(o3) as avg_o3, AVG(co) as avg_co, AVG(so2) as avg_so2, AVG(no2) as avg_no2 , pointName " +
            "FROM tower_ozone_gradient_observations " +
            "WHERE time BETWEEN #{startTime} AND #{endTime} " +
            "AND pointName LIKE CONCAT('%', #{stationName}, '%') " +
            "GROUP BY pointName, DATE_FORMAT(time, '%Y-%m-%d %H:00:00')"+
    "ORDER BY hour_time")
    List<TowerOzoneGradientObservations> getHourlyAverageAllData(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("stationName") String stationName);

    /**
     * 按天分组查询(所有要素)
     */
    @Select("SELECT pointName, AVG(o3) as avg_o3, " +
    "AVG(co) as avg_co, AVG(so2) as avg_so2, AVG(no2) as avg_no2, DATE_FORMAT(time, '%Y-%m-%d') AS day_time " +
            "FROM tower_ozone_gradient_observations " +
            "WHERE time BETWEEN #{startTime} AND #{endTime} " +
            "AND pointName LIKE CONCAT('%', #{stationName}, '%') " +
            "GROUP BY pointName, DATE_FORMAT(time, '%Y-%m-%d')")
    List<TowerOzoneGradientObservations> getDailyAverageAllData(
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime,
            @Param("stationName") String stationName);


}
